﻿using jlynet.core.Library;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.IO;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace jlynet.core.notebook.util
{
    class DBInitialization
    {
        public static void initialization()
        {
            DBInitialization db = new DBInitialization();
            db.create_userInfoTable();
            db.create_problemTable();
            db.create_entryDataTable();
            db.create_directoryTypeDataTable();
            db.create_directoryDataTable();
        }

        /// <summary>
        /// 用户信息表
        /// </summary>
        private void create_userInfoTable()
        {
            SQLiteConfigHelper.tableName = "userInfoTable";
            List<SQLiteColumn> Column = new List<SQLiteColumn>();
            Column.Add(new SQLiteColumn("id_", ColType.Text, true, true, true, ""));
            //文件路径
            Column.Add(new SQLiteColumn("file_path", ColType.Text, false, false, true, ""));
            //用户密码
            Column.Add(new SQLiteColumn("password", ColType.Text, false, false, true, ""));
            //提示问题
            Column.Add(new SQLiteColumn("problem", ColType.Text, false, false, false, ""));
            //提示问题答案
            Column.Add(new SQLiteColumn("answer", ColType.Text, false, false, false, ""));
            Column.Add(new SQLiteColumn("update_time", ColType.Integer, false, false, false, ""));
            Column.Add(new SQLiteColumn("create_time", ColType.Integer, false, false, false, ""));

            MySQLiteHelper.CreateTable(Column);
        }

        /// <summary>
        /// 登陆插入用户信息
        /// </summary>
        /// <param name="password"></param>
        /// <param name="problem"></param>
        /// <param name="answer"></param>
        public static void insert_userInfoTable(string password, string problem, string answer)
        {
            SQLiteConfigHelper.tableName = "userInfoTable";
            var dic = new Dictionary<string, object>();
            DateTime dt = DateTime.Now;
            int time = TimeHelper.ConvertDateTimeInt(dt);
            dic["id_"] = "1";
            Dictionary<string, object> parm = SQLiteUtil.getFileParameter(SQLiteConfigHelper.DatabaseFile);
            dic["file_path"] = parm["filename"];
            dic["password"] = password;
            if (!"".Equals(problem))
            {
                dic["problem"] = problem;
            }
            if (!"".Equals(answer))
            {
                dic["answer"] = answer;
            }
            dic["update_time"] = time;
            dic["create_time"] = time;
            ArrayList arrayList1 = new ArrayList();
            arrayList1.Add(dic);
            long[] rows = MySQLiteHelper.InsertArrayList(arrayList1);
            if (rows!=null && rows.Length>0)
            {
                //MessageBox.Show("userInfoTable 数据插入成功");
            }
        }

        /// <summary>
        /// 提示问题表
        /// </summary>
        private void create_problemTable()
        {
            SQLiteConfigHelper.tableName = "problemTable";
            List<SQLiteColumn> Column = new List<SQLiteColumn>();
            Column.Add(new SQLiteColumn("id_", ColType.Text, true, true, true, ""));
            //提示问题
            Column.Add(new SQLiteColumn("title", ColType.Text, false, false, false, ""));
            Column.Add(new SQLiteColumn("update_time", ColType.Integer, false, false, false, ""));
            Column.Add(new SQLiteColumn("create_time", ColType.Integer, false, false, false, ""));

            if (MySQLiteHelper.CreateTable(Column))
            {
                //MessageBox.Show("成功创建表");
                DateTime dt = DateTime.Now;
                int time = TimeHelper.ConvertDateTimeInt(dt);

                string sql = "INSERT INTO `problemTable` (id_,title,update_time,create_time) VALUES ('1','你姐姐叫什么名字？'," + time + "," + time + "),"
                 + "('2', '你的偶像是谁？', " + time + "," + time + "),"
                 + "('3', '你喜欢谁？', " + time + "," + time + "),"
                 + "('4','你的高中英语老师叫什么名字？'," + time + "," + time + "),"
                 + "('5','你的高中数学老师叫什么名字？'," + time + "," + time + "),"
                 + "('6','你的宠物叫什么名字？'," + time + "," + time + "); ";
                if (MySQLiteHelper.executionSQL(sql)>0)
                {
                    //MessageBox.Show("problemTable 数据插入成功");
                }
            }
        }

        /// <summary>
        /// 创建条目数据表
        /// </summary>
        private void create_entryDataTable()
        {
            SQLiteConfigHelper.tableName = "entryDataTable";
            List<SQLiteColumn> Column = new List<SQLiteColumn>();
            Column.Add(new SQLiteColumn("id_", ColType.Text, true, true, true, ""));
            //条目数据所属目录id
            Column.Add(new SQLiteColumn("directory_id", ColType.Text, false, false, true, ""));
            //标题
            Column.Add(new SQLiteColumn("title", ColType.Text, false, false, true, ""));
            Column.Add(new SQLiteColumn("username", ColType.Text, false, false, false, ""));
            Column.Add(new SQLiteColumn("password_", ColType.Text, false, false, false, ""));
            //url
            Column.Add(new SQLiteColumn("link", ColType.Text, false, false, false, ""));
            //电子邮箱
            Column.Add(new SQLiteColumn("mailbox", ColType.Text, false, false, false, ""));
            //备注
            Column.Add(new SQLiteColumn("remarks", ColType.Text, false, false, false, ""));
            //目录类型
            //Column.Add(new SQLiteColumn("type_", ColType.Text, false, false, true, ""));
            Column.Add(new SQLiteColumn("update_time", ColType.Integer, false, false, false, ""));
            Column.Add(new SQLiteColumn("create_time", ColType.Integer, false, false, false, ""));

            if(MySQLiteHelper.CreateTable(Column)){
                // string sql = "INSERT INTO `entryDataTable` (id_,directory_id,title,username,password_,link,mailbox,remarks,update_time,create_time) VALUES ('1','11','111','111','1111','111','111',NULL,NULL,NULL),('2', '10', 'xxxx', 'xxx', 'xxx', 'xxx', 'xxx', 'xxx', NULL, NULL);";
                // if (MySQLiteHelper.executionSQL(sql)>0)
                // {
                // MessageBox.Show("entryDataTable 数据插入成功");
                // }
            }
        }

        /// <summary>
        /// 创建目录类型数据表
        /// </summary>
        private void create_directoryTypeDataTable()
        {
            SQLiteConfigHelper.tableName = "directoryTypeDataTable";
            List<SQLiteColumn> Column = new List<SQLiteColumn>();
            Column.Add(new SQLiteColumn("id_", ColType.Text, true, true, true, ""));
            //类型名称
            Column.Add(new SQLiteColumn("name_", ColType.Text, false, false, true, ""));
            Column.Add(new SQLiteColumn("update_time", ColType.Integer, false, false, false, ""));
            Column.Add(new SQLiteColumn("create_time", ColType.Integer, false, false, false, ""));
            DateTime dt = DateTime.Now;
            int time = TimeHelper.ConvertDateTimeInt(dt);

            if (MySQLiteHelper.CreateTable(Column))
            {
                string sql = "INSERT INTO `directoryTypeDataTable` (id_, name_, update_time, create_time) VALUES "
                    + "('0', '主类型',  " + time + "," + time + "),"
                    + "('1', '网上信息类型',  " + time + "," + time + "),"
                    + "('2','银行账号类型', " + time + "," + time + "),"
                    + "('3','通用数据类型', " + time + "," + time + ");";
                if (MySQLiteHelper.executionSQL(sql) > 0)
                {
                    //MessageBox.Show("problemTable 数据插入成功");
                }
            }
        }

        /// <summary>
        /// 创建目录数据表
        /// </summary>
        private void create_directoryDataTable()
        {
            SQLiteConfigHelper.tableName = "directoryDataTable";
            List<SQLiteColumn> Column = new List<SQLiteColumn>();
            Column.Add(new SQLiteColumn("id_", ColType.Text, true, true, true, ""));
            //父级id
            Column.Add(new SQLiteColumn("pid", ColType.Text, false, false, false, ""));
            //目录名称
            Column.Add(new SQLiteColumn("name_", ColType.Text, false, false, true, ""));
            //目录类型
            Column.Add(new SQLiteColumn("type_", ColType.Text, false, false, true, ""));
            Column.Add(new SQLiteColumn("update_time", ColType.Integer, false, false, false, ""));
            Column.Add(new SQLiteColumn("create_time", ColType.Integer, false, false, false, ""));

            if (MySQLiteHelper.CreateTable(Column))
            {
                //MessageBox.Show("成功创建表");
                DateTime dt = DateTime.Now;
                int time = TimeHelper.ConvertDateTimeInt(dt);

                string sql = "INSERT INTO `directoryDataTable` (id_,pid,name_,type_,update_time,create_time) VALUES ('1','0','密码箱','0'," + time + "," + time + "),"
                + "('2', '1', '<网上信息>','1', " + time + "," + time + "),"
                + "('3', '2', '电子邮件','1', " + time + "," + time + "),"
                + "('4','2','ＱＱ号码','1'," + time + "," + time + "),"
                + "('5','2','论坛账号','1'," + time + "," + time + "),"
                + "('6','2','游戏账号','1'," + time + "," + time + "),"
                + "('7','2','其它密码','1'," + time + "," + time + "),"
                + " ('8','1','<银行账号>','2'," + time + "," + time + "),"
                + " ('9','8','招商银行','2'," + time + "," + time + "),"
                + "('10','8','中国银行','2'," + time + "," + time + "),"
                + " ('11','8','工商银行','2'," + time + "," + time + "),"
                + " ('12','8','建设银行','2'," + time + "," + time + "),"
                + " ('13','8','农业银行','2'," + time + "," + time + "),"
                + " ('14','8','交通银行','2'," + time + "," + time + "),"
                + "('15','1','<通用数据>','3'," + time + "," + time + "),"
                + " ('16','15','重要纪事','3'," + time + "," + time + "),"
                + " ('17','15','手机相关','3'," + time + "," + time + "),"
                + " ('18','15','经济往来','3'," + time + "," + time + "); ";
                if (MySQLiteHelper.executionSQL(sql)>0)
                {
                    //MessageBox.Show("directoryDataTable 数据插入成功");
                }
            }
        }


    }
}
